﻿using Dt261.Model;
using SQLite;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Dt261.db
{
    //class EroResultDb
    //{

    //    private readonly SQLiteConnection connection;

    //    public EroResultDb(string dbPath)
    //    {
    //        connection = new SQLiteConnection(dbPath);
    //        connection.CreateTable<EroResult>();
    //    }

    //}

    class EroEntityDb
    {

        private readonly SQLiteConnection connection;

        public EroEntityDb(string dbPath)
        {
            connection = new SQLiteConnection(dbPath);
            connection.CreateTable<EroEntity>();
            connection.CreateTable<EroResult>();
            connection.CreateTable<EroChart>();
            connection.CreateTable<EroFrequency>();
        }


        public int AddEroEntity(EroEntity m)
        {
            if (string.IsNullOrWhiteSpace(m.MemberId))
            {
                throw new Exception(" memberId MemberId required!");
                //return 0;
            }
            // 

            foreach(EroResult result in m.results)
            {
                result.Entityid = m.Uid;
                connection.Insert(result);
                foreach (EroFrequency f in result.frequency)
                {
                    f.Resultid = result.Uid;
                    connection.Insert(f);
                }
                foreach (EroChart c in result.chart)
                {
                    c.Resultid = result.Uid;
                    connection.Insert(c);
                }
                
            }

            return connection.Insert(m);
        }


        public IList<EroEntity> GetEroEntitys()
    => connection.Table<EroEntity>().ToList();


       
       public IList<EroEntity> GetALLEroEntitysWithName()
        {
            IList<EroEntity> list = connection.Query<EroEntity>("select EroEntity.*,memberinfo.name as name from EroEntity  LEFT JOIN  memberinfo on  EroEntity.memberId= memberinfo.uid");
            getchild(list);
            return list;
        }
        public IList<EroEntity> GetEroEntityByUid(string uid)
        {
            IList<EroEntity> list = connection.Query<EroEntity>("select * from EroEntity where uid = ?", uid);

            return list;
        }

        public IList<EroEntity> GetEroEntityByMember(string memberid)
        {
            IList<EroEntity> list = connection.Query<EroEntity>("select * from EroEntity where memberId = ?", memberid);

            getchild(list);



            return list;
        }


        private void getchild(IList<EroEntity> list)
        {
            if (list.Count > 0)
            {
                foreach (EroEntity entry in list)
                {
                    entry.results = connection.Query<EroResult>("select * from EroResult where entityid = ? Order By Ear ", entry.Uid);

                    if (entry.results.Count > 0)
                    {
                        foreach (EroResult r in entry.results)
                        {
                            r.chart = connection.Query<EroChart>("select * from EroChart where resultid = ? Order By Time", r.Uid);
                            r.frequency = connection.Query<EroFrequency>("select * from EroFrequency where resultid = ? Order By Sort", r.Uid);
                        }
                    }
                }
            }
        }

        public int DeleteEroEntity(EroEntity m)
        {
            if (m != null)
                return connection.Delete(m);
            return 0;
        }

        public IList<EroEntity> SearchEroEntity(string userid, string key)
        {
            if (key != null)
            {
                if (key == "ALL")
                {
                    //=====
                    IList<EroEntity> list = connection.Query<EroEntity>("select * from EroEntity where name like ?  order by createTime DESC", "%" + "%");
                    return list;
                }
                else
                {
                    //=====
                    string keys = "%" + key + "%";
                    IList<EroEntity> list = connection.Query<EroEntity>("select * from memberinfo where name like ? or  Insurance LIKE ? or Phone LIKE ? and  UserId = ?  order by createTime DESC", keys, keys, keys, userid);
                    return list;
                }

            }

            return null;
        }

    }
}
